<?php
/**
 * 全局失败日志管理
 * @author liangpan
 */
include_once dirname(dirname(__FILE__)).DIRECTORY_SEPARATOR.'public.class.php';
$nns_project_dir = dirname(dirname(dirname(__FILE__)));
require_once $nns_project_dir . DIRECTORY_SEPARATOR ."nn_cms_db".DIRECTORY_SEPARATOR. "nns_common" . DIRECTORY_SEPARATOR . "nns_db_excel_class.php";
class nl_general_log extends nl_public
{
	static $base_table='mysql.general_log';
	static $arr_filed = array(
			'event_time',
			'user_host',
			'thread_id',
			'server_id',
			'command_type',
			'argument',
	);
	
	/**
	 * 查询全局错误日志列表
	 * @param object $dc 数据库对象
	 * @param array $params 查询参数
	 * @param array $page_info 分页信息
	 * @return Ambigous array('ret'=>'状态码','reason'=>'原因','data'=>'数据')
	 * @author liangpan
	 * @date 2016-03-06
	 */
	static public function query($dc,$params,$page_info=null)
	{
	    if(isset($params['where']) && !empty($params['where']))
		{
			foreach ($params['where'] as $where_key=>$where_val)
			{
				if($where_val == '--')
				{
					self::$str_where.=" ( isnull({$where_key}) or {$where_key} = '' ) and ";
				}
				else if($where_key == 'create_begin_time')
				{
					self::$str_where.=" event_time >='$where_val' and ";
				}
				else if($where_key == 'create_end_time')
				{
					self::$str_where.=" event_time <='{$where_val}' and ";
				}
				else
				{
					self::$str_where.=" {$where_key} = '{$where_val}' and ";
				}
			}	
		}
		if(isset($params['other']) && !empty($params['other']))
		{
		    foreach ($params['other'] as $other_val)
		    {
		        if(strlen($other_val) <1)
		        {
		            continue;
		        }
		        self::$str_where.=" {$other_val} and ";
		    }
		}
		if(isset($params['like']) && !empty($params['like']))
		{
			foreach ($params['like'] as $like_key=>$like_val)
			{
				self::$str_where.=" {$like_key} like '%{$like_val}%' and ";
			}
		}
		if(isset($params["filter"]) && !empty($params["filter"]))
		{
			foreach($params["filter"] as $filter_argument)
			{
				self::$str_where.=" locate('{$filter_argument}',argument)=0 and ";
			}
		}
		$str_order = (isset($params['order']) && !empty($params['order']) && is_array($params['order'])) ? " order by ".implode(",", $params['order']) : " order by event_time desc ";
		self::$str_where = (strlen(self::$str_where) > 5) ? trim(self::$str_where,'and ') : '';
		$str_limit = self::make_page_limit($page_info);
		$sql="select * from " . self::$base_table . " " . self::$str_where . " {$str_order} {$str_limit} ";
		$sql_count = "select count(*) as count from " . self::$base_table . " " . self::$str_where;
		$result_count = nl_query_by_db($sql_count,  $dc->db());
		$result = nl_query_by_db($sql, $dc->db());
		self::init_where();
		if(!$result || !$result_count)
		{
			return self::return_data(1,"查询数据失败".$sql);
		}
		return self::return_data(0,"查询数据成功",$result,$result_count);
	}
	
	/**
	 * 依据时间删除日志
	 * @param unknown $dc
	 * @param unknown $date
	 * @return Ambigous array('ret'=>'状态码','reason'=>'原因','data'=>'数据')
	 * @author liangpan
	 * @date 2016-03-06
	 */
	public static function del_date($dc,$date=null)
	{
	    $sql = "truncate " . self::$base_table . " ;";
// 	    $sql="delete from " . self::$base_table . " where start_time <'{$date}' ";
	    $result = nl_execute_by_db($sql, $dc->db());
	    return $result ? self::return_data(0,"ok".$sql) : self::return_data(1,"查询数据失败".$sql);
	}
	
	
	public static function show_state($dc)
	{
	    $sql="show global variables like '%genera%'";
	    $result = nl_query_by_db($sql,  $dc->db());
	    if(!$result)
	    {
	        return self::return_data(1,"查询数据失败".$sql);
	    }
	    return self::return_data(0,"查询数据成功",$result);
	}
	
	public static function opration_log($dc,$op_flag)
	{
	    $sql="set global general_log={$op_flag}";
	    $result = nl_execute_by_db($sql,  $dc->db());
	    if(!$result)
	    {
	        return self::return_data(1,"执行数据失败".$sql);
	    }
	    return self::return_data(0,"执行数据成功",$result);
	}

	/**
	 *设置数据库的配置
	 * @param obj $dc	数据库操作对象
	 * @param int $set_value	配置的值
	 * @param string $operation		配置项
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 * @author  feijian.gao
	 * @date	2017年1月24日15:17:44
	 */
	public static function set_mysql_config($dc, $set_value, $operation)
	{
		$sql = "set {$operation} = {$set_value}";
		$result = nl_execute_by_db($sql,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"执行数据失败".$sql);
		}
		return self::return_data(0,"执行数据成功",$result);
	}

	/**
	 * 查询数据库的variables值，支持条件过滤
	 * @param obj $dc  数据库操作类
	 * @param string $str_param		查询的variables值
	 * @param string $str_where_filter		过滤条件
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 * @author  feijian.gao
	 * @date	2017年1月24日15:17:44
	 */
	public static function show_mysql_parameter($dc,$str_param, $str_where_filter)
	{
		$sql = "show {$str_param}";
		if(!empty($str_where_filter))
		{
			$sql = "show {$str_param} like '%{$str_where_filter}%'";
		}
		$result = nl_query_by_db($sql,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"查询数据失败".$sql);
		}
		return self::return_data(0,"查询数据成功",$result);
	}

	/**
	 * 执行一次SQL
	 * @param  object  $dc
	 * @param  $sql    执行的SQL
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 */
	public static function execute_sql($dc,$sql)
	{
		$result = nl_execute_by_db($sql,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"查询数据失败".$sql);
		}
		return self::return_data(0,"查询数据成功",$result);
	}

	/**
	 * 显示 PROFILE 结果（按时长排序）
	 * @param  object  $dc				数据库操对象
	 * @param  string  $str_query_id   profiling中query_id
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 */
	public static function select_consumption_time_sort($dc, $str_query_id)
	{
		$str_profiling_analyse = "SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec` FROM information_schema.profiling WHERE query_id = {$str_query_id} GROUP BY state ORDER BY `duration (summed) in sec` DESC;";
		$result = nl_query_by_db($str_profiling_analyse,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"查询数据失败".$str_profiling_analyse);
		}
		return self::return_data(0,"查询数据成功",$result);
	}

	/**
	 * 使用explain分析一条查询语句
	 * @param  object  $dc				 数据库操作对象
	 * @param  string  $str_select_sql  查询SQL语句
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 */
	public static function explain_select_sql($dc, $str_select_sql)
	{
		$explain_sql = "explain {$str_select_sql}";
		$result = nl_query_by_db($explain_sql,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"查询数据失败".$explain_sql);
		}
		return self::return_data(0,"查询数据成功",$result);
	}

	/**
	 * 刷新status的数据
	 * @param obj 	  $dc 				数据库操作对象
	 * @param string $flush_operation  flush的对象（privilege或stats）
	 * @return array('ret'=>'返回码','reason'=>'对返回码的说明','data_info'=>array('若有数据则返回结果数据'))
	 */
	public static function mysql_flush_status($dc, $flush_operation)
	{
		$sql = "flush {$flush_operation}";
		$result = nl_execute_by_db($sql,  $dc->db());
		if(!$result)
		{
			return self::return_data(1,"执行数据失败".$sql);
		}
		return self::return_data(0,"执行数据成功",$result);
	}

	public static function get_cml_select_sql($dc)
	{

	}
}
